<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 * 
 * 报表部分
 */
namespace app\cron\controller;
use think\Db;
use app\common\service\OssService;

class Report extends \think\Controller {

    private $_limit = 50000;
    //当前执行脚本以什么日期为准
    private $_current_time = 0;
    private $_companyId = 1;

    public function _initialize() {
        global $argv;
        $this->_current_time = time();
        if (isset($argv[2])) {
            $this->_current_time = strtotime($argv[2]);
        }
        parent::_initialize();
    }

    //验光数据统计-月统计
    public function OptometryMonth() {
        //获取上个月1号，到上个月月底
        $startDay = date('Y-m-01', strtotime('-1 month', $this->_current_time));
        $endDay = date('Y-m-t', strtotime('-1 month', $this->_current_time));
        $filePath = date('Ym', strtotime($startDay));
        $zipName = "OptometryMonth$filePath";
        $filePath = "./Upload/Report/OptometryMonth$filePath";
        if (!file_exists($filePath)) {
            mkdir($filePath, 0777, TRUE) or die('mkdir fail');
        }

        $limit = $this->_limit;
        $page = 0;

        while (++$page) {
            
            $list = Db::table('oms.service_orders')->alias('a')
                            ->field(
                                   "d.staff_name as '职员姓名',
                                    d.staff_job_id as '职员工号',
                                    d.level as '职员等级',
                                    d.star  as '职员星级',
                                    d.price  as '专家费用',
                                    c.store_province as '省',
                                    c.store_city as '市',
                                    c.store_district as '区',
                                    c.store_id as '叽叽门店ID',
                                    c.nom_old_store_id as '方维旧系统中门店的四码',
                                    e.vip_id as '会员ID',
                                    e.Info1 as 'openid',
                                    a.service_name as '验光服务名称',
                                    a.service_price as '服务价格',
                                    f.status_to_p_name as '服务状态',
                                    FROM_UNIXTIME(a.create_time, '%Y-%m-%d %H:%i:%S') as '创建日期',
                                    FROM_UNIXTIME(a.report_visit_time, '%Y-%m-%d %H:%i:%S') as '顾客查阅报告时间',
                                    b.queue_id ,
                                    a.service_id,
                                    a.service_order_status,
                                    a.is_delete,
                                    d.staff_id,
                                    b.create_date,
                                    c.store_show_name"
                             ) 
                            ->join('oms.orders b','a.order_id = b.order_id ', 'left')
                            ->join('oms.stores c','b.store_id = c.store_id ', 'left')
                            ->join('oms.staffs d','b.staff_id = d.staff_id', 'left')                            
                            ->join('jooj_member.jj_member_info_1 e','e.vip_id = b.member_code', 'left')
                            ->join('oms.order_status_relations f',"f.status_value = a.service_order_status and f.company_id = 0 and f.order_type = 'service_orders'", 'left')
                            ->where("b.create_date >= '{$startDay}' and b.create_date <= '{$endDay}'")
                            ->page($page, $limit)->select();
            //导出记录
            $csvFile = '';
            
            foreach ($list as $key => $item) {
                if(substr($item['创建日期'],0,4)=='1970'){
                    $item['创建日期'] = '-';
                }
                if(substr($item['顾客查阅报告时间'],0,4)=='1970'){
                    $item['顾客查阅报告时间'] = '-';
                }
                if ($key == 0) {
                    $csvFile .= implode(',',array_keys($item)) . "\n";
                }
                foreach($item as $ky=>$itm){
                    $item[$ky] = '"'.$itm.'"';
                }
                $csvFile .= implode(',', $item) . "\n";
            }
            if (empty($csvFile)) {
                $csvFile = '当前暂无数据';
            }

            $filename = "OptometryMonth$fileName_" . str_pad($page, 3, 0, STR_PAD_LEFT) . ".csv";
            $csvFile = iconv("UTF-8", "GBK//IGNORE", $csvFile);
            
            file_put_contents($filePath . DIRECTORY_SEPARATOR . $filename, $csvFile);

            //判断是否有记录
            if (empty($list)) {
                break;
            }
        }

        $current_file = dirname($filePath);
        exec("cd $current_file && zip -r $zipName.zip $zipName/*");
        
        $this->pushOSS("$zipName.zip", $current_file . DIRECTORY_SEPARATOR . "$zipName.zip");
    }

    //验光数据统计-日统计
    public function OptometryDay() {

        $startDay = date('Y-m-d', strtotime('-1 day', $this->_current_time));
        $endDay = date('Y-m-d', $this->_current_time);

        $filePath = date('Ym', strtotime($startDay));
        $filePath = "./Upload/Report/OptometryDay$filePath";

        if (!file_exists($filePath)) {
            mkdir($filePath, 0777, TRUE) or die('mkdir fail');
        }

            $list = Db::table('oms.service_orders')->alias('a')
                            ->field(
                                   "d.staff_name as '职员姓名',
                                    d.staff_job_id as '职员工号',
                                    d.level as '职员等级',
                                    d.star  as '职员星级',
                                    d.price  as '专家费用',
                                    c.store_province as '省',
                                    c.store_city as '市',
                                    c.store_district as '区',
                                    c.store_id as '叽叽门店ID',
                                    c.nom_old_store_id as '方维旧系统中门店的四码',
                                    e.vip_id as '会员ID',
                                    e.Info1 as 'openid',
                                    a.service_name as '验光服务名称',
                                    a.service_price as '服务价格',
                                    f.status_to_p_name as '服务状态',
                                    FROM_UNIXTIME(a.create_time, '%Y-%m-%d %H:%i:%S') as '创建日期',
                                    FROM_UNIXTIME(a.report_visit_time, '%Y-%m-%d %H:%i:%S') as '顾客查阅报告时间',
                                    b.queue_id ,
                                    a.service_id,
                                    a.service_order_status,
                                    a.is_delete,
                                    d.staff_id,
                                    b.create_date,
                                    c.store_show_name"
                             ) 
                            ->join('oms.orders b','a.order_id = b.order_id ', 'left')
                            ->join('oms.stores c','b.store_id = c.store_id ', 'left')
                            ->join('oms.staffs d','b.staff_id = d.staff_id', 'left')                            
                            ->join('jooj_member.jj_member_info_1 e','e.vip_id = b.member_code', 'left')
                            ->join('oms.order_status_relations f',"f.status_value = a.service_order_status and f.company_id = 0 and f.order_type = 'service_orders'", 'left')
                            ->where("b.create_date >= '{$startDay}' and b.create_date < '{$endDay}'")
                            ->select();

        //导出记录
        $csvFile = '';
        foreach ($list as $key => $item) {
            if(substr($item['创建日期'],0,4)=='1970'){
                    $item['创建日期'] = '-';
            }
            if(substr($item['顾客查阅报告时间'],0,4)=='1970'){
                $item['顾客查阅报告时间'] = '-';
            }
            if ($key == 0) {
                $csvFile .= implode(',',array_keys($item)) . "\n";
            }
            foreach($item as $ky=>$itm){
                $item[$ky] = '"'.$itm.'"';
            }
            $csvFile .= implode(',', $item) . "\n";
        }

        if (empty($csvFile)) {
            $csvFile = '当前暂无数据';
        }

        $filename = date('Ymd', strtotime($startDay)) . ".csv";
        $csvFile = iconv("UTF-8", "GBK//IGNORE", $csvFile);
        file_put_contents($filePath . DIRECTORY_SEPARATOR . $filename, $csvFile);

        $this->pushOSS("OptometryDay" . $filename, $filePath . DIRECTORY_SEPARATOR . $filename);
    }

    //验光评价统计-按验光日期的评价月度报表-月统计
    public function EvaluateByYgMonth() {

        //获取上个月1号，到上个月月底
        $startDay = date('Y-m-01', strtotime('-1 month', $this->_current_time));
        $endDay = date('Y-m-t', strtotime('-1 month', $this->_current_time)+86400);

        $Ym = date('Ym', strtotime($startDay));
        $zipName = "EvaluateByYgMonth$Ym";
        $filePath = "./Upload/Report/EvaluateByYgMonth$Ym";
        if (!file_exists($filePath)) {
            mkdir($filePath, 0777, TRUE) or die('mkdir fail');
        }

        $limit = $this->_limit;
        $page = 0;

        while (++$page) {
            $list = Db::table('oms.service_orders')->alias('service_orders')
                            ->field("service_orders.service_order_id AS '验光单号',FROM_UNIXTIME(
                        service_orders.complete_time,'%Y年%m月%d日 %H:%i:%S') AS '生成评价单时间',
                        service_orders.member_code AS '会员id',jooj_member.jj_member_info_1.Info1 AS '会员openid',
                        jooj_member.jj_member_1.Name AS  '会员姓名',jooj_member.jj_member_1.MobileNO AS '会员手机号',
                        service_orders.comment_send_status AS '是否发微信通知，0未发 1已发',
                        service_orders.comment_receive_status AS '微信接收状态：0未接收 1已接收 2未接收-用户拒绝 3未接收-非用户拒绝',
                        service_orders.sms_send_status AS '是否发短信通知，0未发 1已发',
                        FROM_UNIXTIME(
                          service_orders.comment_visit_time,
                          '%Y年%m月%d日 %H:%i:%S'
                        ) AS '用户访问评价单时间',
                        service_orders.comment_status AS '评价状态 1已评价 0未评价 -1已过期',
                        FROM_UNIXTIME(
                          service_order_comment.create_time,
                          '%Y年%m月%d日 %H:%i:%S'
                        ) AS '评价时间',
                        service_orders.staff_id AS '验光师id',
                        staffs.staff_name AS '验光师姓名',
                        staffs.staff_job_id AS '验光师工号',
                        staffs.level AS '验光师等级',
                        staffs.star AS '验光师星级',
                        staffs.reserve_status AS '是否有预约权限 1有 0无',
                        staffs.price AS '专家费用',
                        stores.trusteeship_id AS '门店编码',
                        stores.store_show_name AS '验光门店',
                        stores.store_province AS '省',
                        stores.store_city AS '市',
                        stores.bu_name AS '事业部',
                        service_orders.service_name AS '服务项目',
                        service_orders.service_price AS '项目价格',
                        service_orders.last_send_date AS '发送验光报告时间',
                        FROM_UNIXTIME(
                         service_orders.report_visit_time,
                          '%Y年%m月%d日 %H:%i:%S'
                        ) AS '用户访问验光报告时间',
                        service_order_comment.profession_score AS '专业度分数',
                        service_order_comment.attitude_score AS '服务态度分数',
                        service_order_comment.environment_score AS '验光环境分数',
                        service_order_comment.prescription_score AS '处方舒适度分数',
                        service_order_comment.comment_content AS '评价内容',
                        service_order_comment.image_count AS '图片张数',
                        GROUP_CONCAT(
                          service_order_comment_image.image
                        ) AS '图片地址'")
                            ->join('jooj_member.jj_member_1','jooj_member.jj_member_1.vip_id = service_orders.member_code', 'left')
                            ->join('jooj_member.jj_member_info_1','jooj_member.jj_member_info_1.vip_id = service_orders.member_code', 'left')
                            ->join('oms.staffs','staffs.staff_id = service_orders.staff_id', 'left')
                            ->join('oms.orders','service_orders.order_id = orders.order_id', 'left')
                            ->join('oms.stores','stores.store_id = orders.store_id', 'left')
                            ->join('oms.service_order_comment','service_order_comment.service_order_id = service_orders.service_order_id', 'left')
                            ->join('oms.service_order_comment_image','service_order_comment_image.soc_id = service_order_comment.soc_id', 'left')
                            ->where("service_orders.company_id = '1' and service_order_status IN('COMPLETE', 'END') "
                                    . " and service_orders.complete_time between '" . strtotime($startDay) . "' and '" . (strtotime($endDay)+86400) . "' ")
                            ->group('service_orders.service_order_id')
                            ->page($page, $limit)->select();

            //导出记录
            $csvFile = "";

            foreach ($list as $key => $item) {
                if(substr($item['用户访问验光报告时间'],0,4)=='1970'){
                    $item['用户访问验光报告时间'] = '-';
                }
                if(substr($item['评价时间'],0,4)=='1970'){
                    $item['评价时间'] = '-';
                }
                if(substr($item['用户访问评价单时间'],0,4)=='1970'){
                    $item['用户访问评价单时间'] = '-';
                }
                if(substr($item['生成评价单时间'],0,4)=='1970'){
                    $item['生成评价单时间'] = '-';
                }
                if ($key == 0) {
                    $csvFile .= implode(',',array_keys($item)) . "\n";
                }
                foreach($item as $ky=>$itm){
                    $item[$ky] = '"'.$itm.'"';
                }
                $csvFile .= implode(',', $item) . "\n";
            }

            if (empty($csvFile)) {
                $csvFile = '暂无数据';
            }
            
            $filename = "EvaluateByYgMonth$Ym_" . str_pad($page, 3, 0, STR_PAD_LEFT) . ".csv";
            $csvFile = iconv("UTF-8", "GBK//IGNORE", $csvFile);
            file_put_contents($filePath . DIRECTORY_SEPARATOR . $filename, $csvFile);

            //判断是否有记录
            if (empty($list)) {
                break;
            }
        }

        $current_file = dirname($filePath);
        exec("cd $current_file && zip -r $zipName.zip $zipName/*");

        $this->pushOSS("$zipName.zip", $current_file . DIRECTORY_SEPARATOR . "$zipName.zip");
    }

    //验光评价统计-按验光评价的评价月度报表-月统计
    public function EvaluateByPjMonth() {

        //获取上个月1号，到上个月月底
        $startDay = date('Y-m-01', strtotime('-1 month', $this->_current_time));
        $endDay = date('Y-m-t', strtotime('-1 month', $this->_current_time));
        $Ym = date('Ym', strtotime($startDay));
        $zipName = "EvaluateByPjMonth$Ym";
        $filePath = "./Upload/Report/EvaluateByPjMonth$Ym";
        if (!file_exists($filePath)) {
            mkdir($filePath, 0777, TRUE) or die('mkdir fail');
        }

        $limit = $this->_limit;
        $page = 0;

        while (++$page) {
           
            $list = Db::table('oms.service_orders')->alias('service_orders')
                            ->field("service_orders.service_order_id AS '验光单号',FROM_UNIXTIME(
                        service_orders.complete_time,'%Y年%m月%d日 %H:%i:%S') AS '生成评价单时间',
                        service_orders.member_code AS '会员id',jooj_member.jj_member_info_1.Info1 AS '会员openid',
                        jooj_member.jj_member_1.Name AS  '会员姓名',jooj_member.jj_member_1.MobileNO AS '会员手机号',
                        service_orders.comment_send_status AS '是否发微信通知，0未发 1已发',
                        service_orders.comment_receive_status AS '微信接收状态：0未接收 1已接收 2未接收-用户拒绝 3未接收-非用户拒绝',
                        service_orders.sms_send_status AS '是否发短信通知，0未发 1已发',
                        FROM_UNIXTIME(
                          service_orders.comment_visit_time,
                          '%Y年%m月%d日 %H:%i:%S'
                        ) AS '用户访问评价单时间',
                        service_orders.comment_status AS '评价状态 1已评价 0未评价 -1已过期',
                        FROM_UNIXTIME(
                          service_order_comment.create_time,
                          '%Y年%m月%d日 %H:%i:%S'
                        ) AS '评价时间',
                        service_orders.staff_id AS '验光师id',
                        staffs.staff_name AS '验光师姓名',
                        staffs.staff_job_id AS '验光师工号',
                        staffs.level AS '验光师等级',
                        staffs.star AS '验光师星级',
                        staffs.reserve_status AS '是否有预约权限 1有 0无',
                        staffs.price AS '专家费用',
                        stores.trusteeship_id AS '门店编码',
                        stores.store_show_name AS '验光门店',
                        stores.store_province AS '省',
                        stores.store_city AS '市',
                        stores.bu_name AS '事业部',
                        service_orders.service_name AS '服务项目',
                        service_orders.service_price AS '项目价格',
                        service_orders.last_send_date AS '发送验光报告时间',
                        FROM_UNIXTIME(
                         service_orders.report_visit_time,
                          '%Y年%m月%d日 %H:%i:%S'
                        ) AS '用户访问验光报告时间',
                        service_order_comment.profession_score AS '专业度分数',
                        service_order_comment.attitude_score AS '服务态度分数',
                        service_order_comment.environment_score AS '验光环境分数',
                        service_order_comment.prescription_score AS '处方舒适度分数',
                        service_order_comment.comment_content AS '评价内容',
                        service_order_comment.image_count AS '图片张数',
                        GROUP_CONCAT(
                          service_order_comment_image.image
                        ) AS '图片地址'")
                            ->join('jooj_member.jj_member_1','jooj_member.jj_member_1.vip_id = service_orders.member_code', 'left')
                            ->join('jooj_member.jj_member_info_1','jooj_member.jj_member_info_1.vip_id = service_orders.member_code', 'left')
                            ->join('oms.staffs','staffs.staff_id = service_orders.staff_id', 'left')
                            ->join('oms.orders','service_orders.order_id = orders.order_id', 'left')
                            ->join('oms.stores','stores.store_id = orders.store_id', 'left')
                            ->join('oms.service_order_comment','service_order_comment.service_order_id = service_orders.service_order_id', 'left')
                            ->join('oms.service_order_comment_image','service_order_comment_image.soc_id = service_order_comment.soc_id', 'left')
                            ->where("service_orders.company_id = '1' and service_order_status IN('COMPLETE', 'END') "
                                    . " and service_order_comment.create_time between '" . strtotime($startDay) . "' and '" . (strtotime($endDay)+86400) . "' ")
                            ->group('service_orders.service_order_id')
                            ->page($page, $limit)->select();

            //导出记录
            $csvFile = "";

            foreach ($list as $key => $item) {
                if(substr($item['用户访问验光报告时间'],0,4)=='1970'){
                    $item['用户访问验光报告时间'] = '-';
                }
                if(substr($item['评价时间'],0,4)=='1970'){
                    $item['评价时间'] = '-';
                }
                if(substr($item['用户访问评价单时间'],0,4)=='1970'){
                    $item['用户访问评价单时间'] = '-';
                }
                if(substr($item['生成评价单时间'],0,4)=='1970'){
                    $item['生成评价单时间'] = '-';
                }
                if ($key == 0) {
                    $csvFile .= implode(',',array_keys($item)) . "\n";
                }
                foreach($item as $ky=>$itm){
                    $item[$ky] = '"'.$itm.'"';
                }
                $csvFile .= implode(',', $item) . "\n";
            }

            if (empty($csvFile)) {
                $csvFile = '暂无数据';
            }

            $filename = "EvaluateByPjMonth$Ym_" . str_pad($page, 3, 0, STR_PAD_LEFT) . ".csv";
            $csvFile = iconv("UTF-8", "GBK//IGNORE", $csvFile);
            file_put_contents($filePath . DIRECTORY_SEPARATOR . $filename, $csvFile);

            //判断是否有记录
            if (empty($list)) {
                break;
            }
        }

        $current_file = dirname($filePath);
        exec("cd $current_file && zip -r $zipName.zip $zipName/*");

        $this->pushOSS("$zipName.zip", $current_file . DIRECTORY_SEPARATOR . "$zipName.zip");
    }

    //验光评价统计-按验光日期的评价日报表-日统计
    public function EvaluateByYgDay() {

        //获取上个月1号，到上个月月底
        $startDay = date('Y-m-d', strtotime('-1 day', $this->_current_time));
        $endDay = date('Y-m-d', $this->_current_time);

        $Ym = date('Ym', strtotime($startDay));
        $filePath = "./Upload/Report/EvaluateByYgDay$Ym";
        if (!file_exists($filePath)) {
            mkdir($filePath, 0777, TRUE) or die('mkdir fail');
        }

        $list = Db::table('oms.service_orders')->alias('service_orders')
                ->field("service_orders.service_order_id AS '验光单号',FROM_UNIXTIME(
                    service_orders.complete_time,'%Y年%m月%d日 %H:%i:%S') AS '生成评价单时间',
                    service_orders.member_code AS '会员id',jooj_member.jj_member_info_1.Info1 AS '会员openid',
                    jooj_member.jj_member_1.Name AS  '会员姓名',jooj_member.jj_member_1.MobileNO AS '会员手机号',
                    service_orders.comment_send_status AS '是否发微信通知，0未发 1已发',
                    service_orders.comment_receive_status AS '微信接收状态：0未接收 1已接收 2未接收-用户拒绝 3未接收-非用户拒绝',
                    service_orders.sms_send_status AS '是否发短信通知，0未发 1已发',
                    FROM_UNIXTIME(
                      service_orders.comment_visit_time,
                      '%Y年%m月%d日 %H:%i:%S'
                    ) AS '用户访问评价单时间',
                    service_orders.comment_status AS '评价状态 1已评价 0未评价 -1已过期',
                    FROM_UNIXTIME(
                      service_order_comment.create_time,
                      '%Y年%m月%d日 %H:%i:%S'
                    ) AS '评价时间',
                    service_orders.staff_id AS '验光师id',
                    staffs.staff_name AS '验光师姓名',
                    staffs.staff_job_id AS '验光师工号',
                    staffs.level AS '验光师等级',
                    staffs.star AS '验光师星级',
                    staffs.reserve_status AS '是否有预约权限 1有 0无',
                    staffs.price AS '专家费用',
                    stores.trusteeship_id AS '门店编码',
                    stores.store_show_name AS '验光门店',
                    stores.store_province AS '省',
                    stores.store_city AS '市',
                    stores.bu_name AS '事业部',
                    service_orders.service_name AS '服务项目',
                    service_orders.service_price AS '项目价格',
                    service_orders.last_send_date AS '发送验光报告时间',
                    FROM_UNIXTIME(
                     service_orders.report_visit_time,
                      '%Y年%m月%d日 %H:%i:%S'
                    ) AS '用户访问验光报告时间',
                    service_order_comment.profession_score AS '专业度分数',
                    service_order_comment.attitude_score AS '服务态度分数',
                    service_order_comment.environment_score AS '验光环境分数',
                    service_order_comment.prescription_score AS '处方舒适度分数',
                    service_order_comment.comment_content AS '评价内容',
                    service_order_comment.image_count AS '图片张数',
                    GROUP_CONCAT(
                      service_order_comment_image.image
                    ) AS '图片地址'")
                ->join('jooj_member.jj_member_1','jooj_member.jj_member_1.vip_id = service_orders.member_code', 'left')
                ->join('jooj_member.jj_member_info_1','jooj_member.jj_member_info_1.vip_id = service_orders.member_code', 'left')
                ->join('oms.staffs','staffs.staff_id = service_orders.staff_id', 'left')
                ->join('oms.orders','service_orders.order_id = orders.order_id', 'left')
                ->join('oms.stores','stores.store_id = orders.store_id', 'left')
                ->join('oms.service_order_comment','service_order_comment.service_order_id = service_orders.service_order_id', 'left')
                ->join('oms.service_order_comment_image','service_order_comment_image.soc_id = service_order_comment.soc_id', 'left')
                ->where("service_orders.company_id = '1' and service_order_status IN('COMPLETE', 'END') "
                        . " and service_orders.complete_time between '" . strtotime($startDay) . "' and '" . strtotime($endDay) . "' ")
                ->group('service_orders.service_order_id')
                ->select();

        //导出记录
        $csvFile = "";

        foreach ($list as $key => $item) {
            if(substr($item['用户访问验光报告时间'],0,4)=='1970'){
                    $item['用户访问验光报告时间'] = '-';
                }
            if(substr($item['评价时间'],0,4)=='1970'){
                $item['评价时间'] = '-';
            }
            if(substr($item['用户访问评价单时间'],0,4)=='1970'){
                $item['用户访问评价单时间'] = '-';
            }
            if(substr($item['生成评价单时间'],0,4)=='1970'){
                    $item['生成评价单时间'] = '-';
                }
            if ($key == 0) {
                $csvFile .= implode(',',array_keys($item)) . "\n";
            }
            foreach($item as $ky=>$itm){
                $item[$ky] = '"'.$itm.'"';
            }
            $csvFile .= implode(',', $item) . "\n";
        }

        if (empty($csvFile)) {
            $csvFile = '当前暂无数据';
        }

        $Ymd = date('Ymd', strtotime($startDay));
        $filename = "$Ymd.csv";
        $csvFile = iconv("UTF-8", "GBK//IGNORE", $csvFile);
        file_put_contents($filePath . DIRECTORY_SEPARATOR . $filename, $csvFile);

        $this->pushOSS("EvaluateByYgDay" . $filename, $filePath . DIRECTORY_SEPARATOR . $filename);
    }

    //验光评价统计-按验光评价的评价日报表-日统计
    public function EvaluateByPjDay() {

        //获取上个月1号，到上个月月底
        $startDay = date('Y-m-d', strtotime('-1 day', $this->_current_time));
        $endDay = date('Y-m-d', $this->_current_time);

        $Ym = date('Ym', strtotime($startDay));
        $filePath = "./Upload/Report/EvaluateByPjDay$Ym";
        if (!file_exists($filePath)) {
            mkdir($filePath, 0777, TRUE) or die('mkdir fail');
        }

        $list = Db::table('oms.service_orders')->alias('service_orders')
                ->field("service_orders.service_order_id AS '验光单号',FROM_UNIXTIME(
                    service_orders.complete_time,'%Y年%m月%d日 %H:%i:%S') AS '生成评价单时间',
                    service_orders.member_code AS '会员id',jooj_member.jj_member_info_1.Info1 AS '会员openid',
                    jooj_member.jj_member_1.Name AS  '会员姓名',jooj_member.jj_member_1.MobileNO AS '会员手机号',
                    service_orders.comment_send_status AS '是否发微信通知，0未发 1已发',
                    service_orders.comment_receive_status AS '微信接收状态：0未接收 1已接收 2未接收-用户拒绝 3未接收-非用户拒绝',
                    service_orders.sms_send_status AS '是否发短信通知，0未发 1已发',
                    FROM_UNIXTIME(
                      service_orders.comment_visit_time,
                      '%Y年%m月%d日 %H:%i:%S'
                    ) AS '用户访问评价单时间',
                    service_orders.comment_status AS '评价状态 1已评价 0未评价 -1已过期',
                    FROM_UNIXTIME(
                      service_order_comment.create_time,
                      '%Y年%m月%d日 %H:%i:%S'
                    ) AS '评价时间',
                    service_orders.staff_id AS '验光师id',
                    staffs.staff_name AS '验光师姓名',
                    staffs.staff_job_id AS '验光师工号',
                    staffs.level AS '验光师等级',
                    staffs.star AS '验光师星级',
                    staffs.reserve_status AS '是否有预约权限 1有 0无',
                    staffs.price AS '专家费用',
                    stores.trusteeship_id AS '门店编码',
                    stores.store_show_name AS '验光门店',
                    stores.store_province AS '省',
                    stores.store_city AS '市',
                    stores.bu_name AS '事业部',
                    service_orders.service_name AS '服务项目',
                    service_orders.service_price AS '项目价格',
                    service_orders.last_send_date AS '发送验光报告时间',
                    FROM_UNIXTIME(
                     service_orders.report_visit_time,
                      '%Y年%m月%d日 %H:%i:%S'
                    ) AS '用户访问验光报告时间',
                    service_order_comment.profession_score AS '专业度分数',
                    service_order_comment.attitude_score AS '服务态度分数',
                    service_order_comment.environment_score AS '验光环境分数',
                    service_order_comment.prescription_score AS '处方舒适度分数',
                    service_order_comment.comment_content AS '评价内容',
                    service_order_comment.image_count AS '图片张数',
                    GROUP_CONCAT(
                      service_order_comment_image.image
                    ) AS '图片地址'")
                ->join('jooj_member.jj_member_1','jooj_member.jj_member_1.vip_id = service_orders.member_code', 'left')
                ->join('jooj_member.jj_member_info_1','jooj_member.jj_member_info_1.vip_id = service_orders.member_code', 'left')
                ->join('oms.staffs','staffs.staff_id = service_orders.staff_id', 'left')
                ->join('oms.orders','service_orders.order_id = orders.order_id', 'left')
                ->join('oms.stores','stores.store_id = orders.store_id', 'left')
                ->join('oms.service_order_comment','service_order_comment.service_order_id = service_orders.service_order_id', 'left')
                ->join('oms.service_order_comment_image','service_order_comment_image.soc_id = service_order_comment.soc_id', 'left')
                ->where("service_orders.company_id = '1' and service_order_status IN('COMPLETE', 'END') "
                        . " and service_order_comment.create_time between '" . strtotime($startDay) . "' and '" . strtotime($endDay) . "' ")
                ->group('service_orders.service_order_id')
                ->select();

        //导出记录
        $csvFile = "";

        foreach ($list as $key => $item) {
            if(substr($item['用户访问验光报告时间'],0,4)=='1970'){
                    $item['用户访问验光报告时间'] = '-';
                }
            if(substr($item['评价时间'],0,4)=='1970'){
                $item['评价时间'] = '-';
            }
            if(substr($item['用户访问评价单时间'],0,4)=='1970'){
                $item['用户访问评价单时间'] = '-';
            }
            if(substr($item['生成评价单时间'],0,4)=='1970'){
                    $item['生成评价单时间'] = '-';
            }
            if ($key == 0) {
                $csvFile .= implode(',',array_keys($item)) . "\n";
            }
            foreach($item as $ky=>$itm){
                $item[$ky] = '"'.$itm.'"';
            }
            $csvFile .= implode(',', $item) . "\n";
        }

        if (empty($csvFile)) {
            $csvFile = '当前暂无数据';
        }

        $Ymd = date('Ymd', strtotime($startDay));
        $filename = "$Ymd.csv";
        $csvFile = iconv("UTF-8", "GBK//IGNORE", $csvFile);
        file_put_contents($filePath . DIRECTORY_SEPARATOR . $filename, $csvFile);

        $this->pushOSS("EvaluateByPjDay" . $filename, $filePath . DIRECTORY_SEPARATOR . $filename);
    }

    //月预约
    public function ArrangeMonth() {
        //获取上个月1号，到上个月月底
        $startDay = date('Y-m-01', strtotime('-1 month', $this->_current_time));
        $endDay = date('Y-m-t', strtotime('-1 month', $this->_current_time));

        $Ym = date('Ym', strtotime($startDay));
        $zipName = "ArrangeMonth$Ym";
        $filePath = "./Upload/Report/ArrangeMonth$Ym";
        if (!file_exists($filePath)) {
            mkdir($filePath, 0777, TRUE) or die('mkdir fail');
        }

        $limit = $this->_limit;
        $page = 0;
        while (++$page) {

            $list = Db::table('oms.member_reserve')->alias('a')
                            ->field("
                            b.staff_name as '职员姓名', 
                            b.staff_job_id as '职员工号', 
                            b.level as '职员等级', 
                            b.star as '职员星级',
                            b.price as '专家费用', 
                            d.store_id as '叽叽门店ID', 
                            d.store_show_name as '门店显示名称',
                            d.store_province as '省', 
                            d.store_city as '市', 
                            d.store_district as '区', 
                            a.reserve_source_id as '预约来源', 
                            e.reserve_status_name as '预约状态',
                            a.reserve_contacts as '顾客姓名',
                            a.reserve_mobile as '顾客手机号',
                            a.reserve_remark as '顾客需求', 
                            a.reserve_cancel_reason as '顾客取消理由',  
                            FROM_UNIXTIME(a.create_time, '%Y-%m-%d %H:%i:%S') as 提交预约时间,
                            FROM_UNIXTIME(a.cancel_time, '%Y-%m-%d %H:%i:%S') as '预约取消时间',  
                            a.member_code, 
                            a.staff_id,
                            a.staff_schedule_id, 
                            c.year, 
                            c.month, 
                            c.day, 
                            c.from_time, 
                            c.to_time, 
                            c.service_date, 
                            a.reserve_status_id ")
                            ->join('oms.staffs b','a.staff_id = b.staff_id', 'left')
                            ->join('oms.staff_schedules c','a.staff_schedule_id = c.staff_schedule_id ', 'left')
                            ->join('oms.stores d ','c.store_id = d.store_id ', 'left')
                            ->join('oms.reserve_statuses e ','e.reserve_status_id = a.reserve_status_id ', 'left')
                            ->where("c.service_date > UNIX_TIMESTAMP('$startDay') AND c.service_date <( UNIX_TIMESTAMP('$endDay')+86400) AND a.company_id = $this->_companyId")
                            ->page($page, $limit)->select();        
            //导出记录
            $csvFile = "";
            foreach ($list as $key => $item) {
                if(substr($item['提交预约时间'],0,4)=='1970'){
                    $item['提交预约时间'] = '-';
                }
                if(substr($item['预约取消时间'],0,4)=='1970'){
                    $item['预约取消时间'] = '-';
                }
                $order_time = $list[$key]['year'].'年'.$list[$key]['month'].'月'.$list[$key]['day'].'日 '.substr($list[$key]['from_time'],2).':'.substr($list[$key]['from_time'],2,2).'-'.substr($list[$key]['to_time'],2).':'.substr($list[$key]['to_time'],2,2);
               // array_splice($list[$key], 10,0,['预约时间'=>$order_time]);
                if ($key == 0) {
                    $csvFile .= '预约时间,'.implode(',',array_keys($item)) . "\n";
                }
                $csvFile .= $order_time.',';
                foreach($item as $ky=>$itm){
                    $item[$ky] = '"'.$itm.'"';
                }
                $csvFile .= implode(',', $item) . "\n";
            }
            if (empty($csvFile)) {
                $csvFile = '暂无数据';
            }

            $filename = "ArrangeMonth$Ym_" . str_pad($page, 3, 0, STR_PAD_LEFT) . ".csv";
            $csvFile = iconv("UTF-8", "GBK//IGNORE", $csvFile);
            file_put_contents($filePath . DIRECTORY_SEPARATOR . $filename, $csvFile);

            //判断是否有记录
            if (empty($list)) {
                break;
            }
        }

        $current_file = dirname($filePath);
        exec("cd $current_file && zip -r $zipName.zip $zipName/*");

        $this->pushOSS("$zipName.zip", $current_file . DIRECTORY_SEPARATOR . "$zipName.zip");
    }

    //日预约
    public function ArrangeDay() {
        //获取上个月1号，到上个月月底
        $startDay = date('Y-m-d', strtotime('-1 day', $this->_current_time));
        $endDay = date('Y-m-d', $this->_current_time);

        $Ym = date('Ym', strtotime($startDay));
        $zipName = "ArrangeDay$Ym";
        $filePath = "./Upload/Report/ArrangeDay$Ym";
        if (!file_exists($filePath)) {
            mkdir($filePath, 0777, TRUE) or die('mkdir fail');
        }

           $list = Db::table('oms.member_reserve')->alias('a')
                            ->field("
                            b.staff_name as '职员姓名', 
                            b.staff_job_id as '职员工号', 
                            b.level as '职员等级', 
                            b.star as '职员星级',
                            b.price as '专家费用', 
                            d.store_id as '叽叽门店ID', 
                            d.store_show_name as '门店显示名称',
                            d.store_province as '省', 
                            d.store_city as '市', 
                            d.store_district as '区', 
                            a.reserve_source_id as '预约来源', 
                            e.reserve_status_name as '预约状态',
                            a.reserve_contacts as '顾客姓名',
                            a.reserve_mobile as '顾客手机号',
                            a.reserve_remark as '顾客需求', 
                            a.reserve_cancel_reason as '顾客取消理由',  
                            FROM_UNIXTIME(a.create_time, '%Y-%m-%d %H:%i:%S') as 提交预约时间,
                            FROM_UNIXTIME(a.cancel_time, '%Y-%m-%d %H:%i:%S') as '预约取消时间', 
                            a.member_code, 
                            a.staff_id,
                            a.staff_schedule_id, 
                            c.year, 
                            c.month, 
                            c.day, 
                            c.from_time, 
                            c.to_time, 
                            c.service_date, 
                            a.reserve_status_id ")
                            ->join('oms.staffs b','a.staff_id = b.staff_id', 'left')
                            ->join('oms.staff_schedules c','a.staff_schedule_id = c.staff_schedule_id ', 'left')
                            ->join('oms.stores d ','c.store_id = d.store_id ', 'left')
                            ->join('oms.reserve_statuses e ','e.reserve_status_id = a.reserve_status_id ', 'left')
                            ->where("c.service_date > UNIX_TIMESTAMP('$startDay') AND c.service_date < UNIX_TIMESTAMP('$endDay') AND a.company_id = $this->_companyId")
                            ->select();   
            //导出记录
            $csvFile = "";
            foreach ($list as $key => $item) {
                if(substr($item['提交预约时间'],0,4)=='1970'){
                    $item['提交预约时间'] = '-';
                }
                if(substr($item['预约取消时间'],0,4)=='1970'){
                    $item['预约取消时间'] = '-';
                }
                $order_time = $list[$key]['year'].'年'.$list[$key]['month'].'月'.$list[$key]['day'].'日 '.substr($list[$key]['from_time'],2).':'.substr($list[$key]['from_time'],2,2).'-'.substr($list[$key]['to_time'],2).':'.substr($list[$key]['to_time'],2,2);
               // array_splice($list[$key], 10,0,['预约时间'=>$order_time]);
                if ($key == 0) {
                    $csvFile .= '预约时间,'.implode(',',array_keys($item)) . "\n";
                }
                $csvFile .= $order_time.',';
                foreach($item as $ky=>$itm){
                    $item[$ky] = '"'.$itm.'"';
                }
                $csvFile .= implode(',', $item) . "\n";
            }
            if (empty($csvFile)) {
                $csvFile = '暂无数据';
            }

        $Ymd = date('Ymd', strtotime($startDay));
        $filename = "$Ymd.csv";
        $csvFile = iconv("UTF-8", "GBK//IGNORE", $csvFile);
        file_put_contents($filePath . DIRECTORY_SEPARATOR . $filename, $csvFile);

        $this->pushOSS('ArrangeDay' . $filename, $filePath . DIRECTORY_SEPARATOR . $filename);
    }

    //推送oss
    public function pushOSS($filename, $filePath) {

        $ossService = new OssService();
        //重试3次
        $times = 3;
        while ($times--){
           try{
                $result = $ossService->fileUpload("file/".date('Ym')."/$filename", file_get_contents($filePath));
                if($result){
                    $position = strpos($filename, '.');
                    $notExtName = substr($filename, 0, $position);
                    $arr = [];
                    preg_match("/([a-zA-z]+)(\d+)/", $notExtName, $arr);
            
                    $reportUrlRecordModel = new \app\common\model\log\ReportUrlRecord();
                    $reportUrlRecordModel->addRecord([
                        'type' => isset($arr[1]) ? $arr[1] : '',
                        'date' => isset($arr[2]) ? $arr[2] : '',
                        'url' => isset($result) ? $result : '',
                    ]);
                    
                    die("$filename\n");
                }else{
                   die("$times\n");
                }    
            } catch (\Exception $ex){
                var_dump($ex->getMessage());
                continue;
            }
        }
        
        die("$times\n");
    }

}
